package com.org.core.dao;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.cglib.beans.BeanMap;

import com.org.core.constant.Pager;
import com.org.core.container.ObjectSqlContainer;
import com.org.core.utils.BeansUtil;
import com.org.core.utils.SqlUtil;
import com.org.core.utils.StringUtil;

public class BaseDaoCiglib<T> extends CommonDao{
	private static Log log = LogFactory.getLog(BaseDaoCiglib.class);
	private Class<T> entityClass;
	
    @SuppressWarnings("unchecked")
    public BaseDaoCiglib(){
        this.entityClass = getSuperClassGenricType(getClass(), 0);
    }

    @SuppressWarnings("rawtypes")
    private Class getSuperClassGenricType(final Class clazz,
            final int index) {
        Type genType = clazz.getGenericSuperclass();

        if (!(genType instanceof ParameterizedType)) {
        	log.error(clazz.getSimpleName() + "'s superclass not ParameterizedType");
            System.err.println(clazz.getSimpleName() + "'s superclass not ParameterizedType");
            return Object.class;
        }

        Type[] params = ((ParameterizedType) genType).getActualTypeArguments();

        if (index >= params.length || index < 0) {
        	log.error("Index: " + index + ", Size of "
                    + clazz.getSimpleName() + "'s Parameterized Type: "
                    + params.length);
            System.err.println("Index: " + index + ", Size of "
                    + clazz.getSimpleName() + "'s Parameterized Type: "
                    + params.length);
            return Object.class;
        }
        if (!(params[index] instanceof Class)) {
        	log.error(clazz.getSimpleName() + " not set the actual class on superclass generic parameter");
            System.out.println(clazz.getSimpleName() + " not set the actual class on superclass generic parameter");
            return Object.class;
        }

        return (Class) params[index];
    }
    
	public List<T> getAll() {
		return getAll(null);
	}
	
	public List<T> getAll(Map<String, Object> params) {
		String sql = ObjectSqlContainer.getSelectSql(entityClass.getName(), params);
		if(StringUtils.isBlank(sql)) {
			throw new RuntimeException("根据查询条件自动创建sql失败: " + entityClass.getName());
		}
		Map<Integer, Object> _params = stringToIntegerMap(params);
		return getList(sql, _params);
	}
	
	public List<T> getAll(Map<String, Object> params, Transaction ts) throws SQLException {
		String sql = ObjectSqlContainer.getSelectSql(entityClass.getName(), params);
		if(StringUtils.isBlank(sql)) {
			throw new RuntimeException("根据查询条件自动创建sql失败: " + entityClass.getName());
		}
		Map<Integer, Object> _params = stringToIntegerMap(params);
		return getList(sql, _params, ts);
	}
	
	public List<T> findByPager(Pager pager) {
		String sql = ObjectSqlContainer.getSelectSqlByPager(entityClass.getName(), pager);
		if(StringUtils.isBlank(sql)) {
			throw new RuntimeException("根据查询条件自动创建sql失败: " + entityClass.getName());
		}
		Map<Integer, Object> _params = new HashMap<Integer, Object>();
		_params.put(1, pager.getStart());
		_params.put(2, pager.getPageCount());
		return getList(sql, _params);
	}
	
	/**
	 * 原生sql查询
	 * @param sql
	 * @param params 没有可填null
	 * @return
	 * @throws SQLException
	 */
	public List<T> getList(String sql, Map<Integer, Object> params) {
		
		java.sql.Connection connection = null;
		List<T> list = new ArrayList<T>();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try{
			connection = getConnectionBak();
			ps = connection.prepareStatement(sql);
			if(params != null) {
				setStatmentParams(ps, params);
			}
			rs = ps.executeQuery();
			
			T entity = null;
			Map<String, Object> res = null;
			while (rs.next()) {
				// 一个指针对应一个entity
				entity = BeansUtil.createBeanJdk(entityClass);
				res = rsToMap(rs);
				BeanMap.create(entity).putAll(res);
				list.add(entity);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				releaseAll(rs, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	/**
	 * 查询后只关闭结果集和ps， 不关闭connection,便于事务管理
	 * @param sql
	 * @param params 没有可填null
	 * @return
	 * @throws SQLException
	 */
	public List<T> getList(String sql, Map<Integer, Object> params, Transaction ts) throws SQLException {
		List<T> list = new ArrayList<T>();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try{
			java.sql.Connection connection = ts.getConnectionBak();
			ps = connection.prepareStatement(sql);
			if(params != null) {
				setStatmentParams(ps, params);
			}
			rs = ps.executeQuery();
			
			T entity = null;
			Map<String, Object> res = null;
			while (rs.next()) {
				// 一个指针对应一个entity
				entity = BeansUtil.createBeanJdk(entityClass);
				res = rsToMap(rs);
				BeanMap.create(entity).putAll(res);
				list.add(entity);
			}
		}finally{
			releaseAll(rs, ps);
		}
		return list;
	}
	
	private T getSingle(String sql, Map<Integer, Object> params) {
		java.sql.Connection connection = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		T entity = null;
		try{
			connection = getConnectionBak();
			ps = connection.prepareStatement(sql);
			setStatmentParams(ps, params);
			rs = ps.executeQuery();
			while(rs.next()){
				entity = BeansUtil.createBeanJdk(entityClass);
				Map<String, Object> res = rsToMap(rs);
				BeanMap.create(entity).putAll(res);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				releaseAll(rs, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}			
		return entity;
	}
	
	public T getSingle(Map<String, Object> params) {
		String sql = ObjectSqlContainer.getSelectSql(entityClass.getName(), params);
		
		Map<Integer, Object> _params = stringToIntegerMap(params);
		return getSingle(sql, _params);
	}
	
	public T get(Integer id) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		return getSingle(params);
	}
	
	public void delete(Integer id) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		delete(params);
	}
	
	public void deleteAll() {
		String sql = ObjectSqlContainer.getDeleteSql(entityClass.getName(), null);
		java.sql.Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.execute();
			connection.commit();
		} catch (SQLException e1) {
			e1.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			try {
				releaseAll(null, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void deleteAll(Transaction ts) throws SQLException {
		String sql = ObjectSqlContainer.getDeleteSql(entityClass.getName(), null);
		java.sql.Connection connection = ts.getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.execute();
			connection.commit();
		} finally {
			releaseAll(null, ps);
		}
	}
	
	public void delete(Map<String, Object> params) {
		String sql = ObjectSqlContainer.getDeleteSql(entityClass.getName(), params);
		Map<Integer, Object> _params = stringToIntegerMap(params);
		
		java.sql.Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			setStatmentParams(ps, _params);
			ps.execute();
			connection.commit();
		} catch (SQLException e1) {
			e1.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			try {
				releaseAll(null, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void delete(Map<String, Object> params, Transaction ts) throws SQLException {
		String sql = ObjectSqlContainer.getDeleteSql(entityClass.getName(), params);
		Map<Integer, Object> _params = stringToIntegerMap(params);
		
		java.sql.Connection connection = ts.getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			setStatmentParams(ps, _params);
			ps.execute();
		} finally {
			releaseAll(null, ps);
		}
	}
	
	public Integer getCount(String sql, Map<Integer, Object> params, String key){
		java.sql.Connection connection = null;
		ResultSet rs = null;
		PreparedStatement ps = null;

		Integer returnInteger = null;
		try{
			connection = getConnectionBak();
			ps = connection.prepareStatement(sql);
			if(params != null){
				setStatmentParams(ps, params);
			}
			rs = ps.executeQuery();
			while(rs.next()){
				returnInteger = rs.getInt(key);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				releaseAll(rs, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return returnInteger;
	}
	
	public Integer getCount(String sql, Map<Integer, Object> params, String key, Transaction ts) throws SQLException{
		java.sql.Connection connection = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		
		Integer returnInteger = null;
		try{
			connection = ts.getConnectionBak();
			ps = connection.prepareStatement(sql);
			if(params != null){
				setStatmentParams(ps, params);
			}
			rs = ps.executeQuery();
			while(rs.next()){
				returnInteger = rs.getInt(key);
			}
		}finally{
			releaseAll(rs, ps);
		}
		return returnInteger;
	}

	private void releaseAll(ResultSet rs, PreparedStatement ps,
			java.sql.Connection connection) throws SQLException {
		if(rs != null){
			rs.close();
		}
		if(ps != null){
			ps.close();
		}
		if(connection != null){
			connection.close();
		}
	}
	
	private void releaseAll(ResultSet rs, PreparedStatement ps) throws SQLException {
		if(rs != null){
			rs.close();
		}
		if(ps != null){
			ps.close();
		}
	}

	protected void setStatmentParams(PreparedStatement ps, Map<Integer, Object> params) throws SQLException{
		if(params == null || params.size() <= 0) {
			return;
		}
		for (Iterator<Integer> iterator = params.keySet().iterator(); iterator
				.hasNext();) {
			Integer key = iterator.next();
			ps.setObject(key, params.get(key));
		}
	}

	protected static Map<String, Object> rsToMap(ResultSet rs) throws SQLException {
		
		ResultSetMetaData rsmd = rs.getMetaData();
		// 列数
		int columnCounts = rsmd.getColumnCount();
		
		Map<String, Object> map = new HashMap<String, Object>();
		String key = null;
		Object value = null;
		for (int i = 1; i <= columnCounts; i++) {
			key = rsmd.getColumnName(i);
			// log.debug(key + " : " + rsmd.getColumnTypeName(i));
			// 转驼峰
			key = StringUtil.toEntityName(key, true);
			// 首字母大写
			//key = String.valueOf(key.charAt(0)).toUpperCase() + key.substring(1);
			value = rs.getObject(i);
			if(value != null && value != "") {
				map.put(key, value);
			}
		}
		return map;
	}

	/**
	 * 给个conn， 不提交，不关闭，用于等待下次数据处理
	 * @param sql
	 * @param params
	 *            ?,?,? {1:"...", 2:"...", 3:"..."}
	 * @return
	 * @return
	 * @throws SQLException
	 */
	public Integer addSingle(String sql, Map<Integer, Object> params, Transaction ts) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer id = null;
		try {
			Connection connection = ts.getConnection();
			ps = connection.prepareStatement(sql);
			setStatmentParams(ps, params);
			ps.executeUpdate();

			ps = connection.prepareStatement("select LAST_INSERT_ID() as lastId");
			rs = ps.executeQuery();
			if(rs.next()) {
				id = rs.getInt("lastId");
			}
		} finally {
			releaseAll(rs, ps);
		}
		return id;
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 *            ?,?,? {1:"...", 2:"...", 3:"..."}
	 * @return
	 * @return
	 * @throws SQLException
	 */
	public Integer addSingle(String sql, Map<Integer, Object> params) {
		java.sql.Connection connection = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer id = 0;
		int count = 0;
		try {
			ps = connection.prepareStatement(sql);
			setStatmentParams(ps, params);
			count = ps.executeUpdate();
			if(count > 0) {
				
				ps = connection.prepareStatement("select LAST_INSERT_ID() as lastId");
				rs = ps.executeQuery();
				if(rs.next()) {
					id = rs.getInt("lastId");
				}
				connection.commit();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				releaseAll(null, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}
	
	/**
	 * 
	 * @param obj 参数对象
	 * @return
	 * @throws SQLException
	 */
	public Integer save(T obj) {
		String sql = ObjectSqlContainer.getInsertSql(obj.getClass().getName());
		Map<Integer, Object> params = SqlUtil.createParamsBy(obj);
		log.info(params.toString());
		return addSingle(sql, params);
	}
	/**
	 * 配合transaction对象，进行事务控制
	 * @param obj 参数对象
	 * @return
	 * @throws SQLException
	 */
	public Integer save(T obj, Transaction ts) throws SQLException {
		String sql = ObjectSqlContainer.getInsertSql(obj.getClass().getName());
		Map<Integer, Object> params = SqlUtil.createParamsBy(obj);
		Integer id = addSingle(sql, params, ts);
		return id;
	}
	
	public boolean batchInsertList(List<T> list){
		return transactionInsertList(list);
	}
	
	
	/**
	 * 批量添加(事务机制保持)
	 * @param list
	 * @return
	 */
	public boolean transactionInsertList(List<T> list){
		String sql;
		sql = ObjectSqlContainer.getInsertSql(list.get(0).getClass().getName());
		Map<Integer, Object> params;
		List<Map<Integer, Object>> paramsList = new ArrayList<Map<Integer,Object>>();
		
		for (Object obj : list) {
			params = SqlUtil.createParamsBy(obj);
			paramsList.add(params);
		}
		return transactionInsert(sql, paramsList);
	}
	
	/**
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
	public boolean update(String sql, Map<Integer, Object> params)  {
		java.sql.Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			setStatmentParams(ps, params);
			ps.executeUpdate();
			connection.commit();
			return true;
		} catch (SQLException e1) {
			e1.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		} finally {
			try {
				releaseAll(null, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
	public boolean update(String sql, Map<Integer, Object> params, Transaction ts) throws SQLException  {
		java.sql.Connection connection = ts.getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		try {
			setStatmentParams(ps, params);
			ps.executeUpdate();
			connection.commit();
		}  finally {
			releaseAll(null, ps);
		}
		return true;
	}
	
	/**
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
	public boolean update(T obj)  {
		String sql = ObjectSqlContainer.getUpdateSql(obj.getClass().getName());
		Map<Integer, Object> params = SqlUtil.createUpdateParamsBy(obj);
		return update(sql, params);
	}

	/**
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
	public boolean update(T obj, Transaction ts) throws SQLException  {
		String sql = ObjectSqlContainer.getUpdateSql(obj.getClass().getName());
		Map<Integer, Object> params = SqlUtil.createUpdateParamsBy(obj);
		return update(sql, params, ts);
	}
	
	/**
	 * 事务插入。建议不大于10000条
	 * 参考backup文件，关于表不支持事务的解决方案
	 * @param sql
	 * @param paramsList
	 */
	public boolean transactionInsert(String sql, List<Map<Integer, Object>> paramsList){
		java.sql.Connection connection = getConnection();
		
		PreparedStatement ps = null;
		try {
			for (int i = 0; i < paramsList.size(); i++) {
				ps = connection.prepareStatement(sql);
				setStatmentParams(ps, paramsList.get(i));
				ps.executeUpdate();
			}
			connection.commit();
			return true;
		} catch (SQLException e1) {
			e1.printStackTrace();
			
			if(connection != null) {
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return false;
		} finally {
			try {
				releaseAll(null, ps, connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private Map<Integer, Object> stringToIntegerMap(Map<String, Object> params){
		Map<Integer, Object> _params = null;
		if(params != null) {
			int index = 1;
			String key = null;
			Object val = null;
			_params = new HashMap<Integer, Object>();
			for (Iterator<?> iterator = params.keySet().iterator(); iterator.hasNext();) {
				key = (String) iterator.next();
				val = params.get(key);
				_params.put(index, val);
				index ++;
			}
		}
		return _params;
	}
	
	public JSONObject sum(List<T> list, String... sumColumns) {
		JSONObject res = new JSONObject();
		if(list == null || list.size() <= 0) {
			return res;
		}
		String key = null;
		String keyMethodName = null;
		Object val = null;
		Integer total = null;
		for (int i = 0; i < sumColumns.length; i++) {
			key = sumColumns[i];
			keyMethodName = StringUtil.toGetter(key);
			Method mtd = null;
			total = 0;
			for (T entity : list) {
				try {
					if(mtd == null) {
						mtd = entity.getClass().getMethod(keyMethodName);
					}
					//Field f = entity.getClass().getDeclaredField(key);
					//getType().getName().equals("BigDecimal");
					val = mtd.invoke(entity);
					if(val != null && StringUtils.isNotEmpty(val.toString())) {
						total = total + Integer.valueOf(val.toString());
					}
				} catch (SecurityException | IllegalArgumentException | NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
					e.printStackTrace();
				}
			}
			res.put(key, total);
		}
		return res;
	}
	
}
